package a00800658.dbtool;

import java.awt.Cursor;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Properties;
import java.util.Vector;

import a00800658.dbtool.data.DatabaseAccessObject;
import a00800658.dbtool.view.ApplicationFrame;

public class Driver {
	
	static DatabaseAccessObject dbAccess;
	static ApplicationFrame appFrame;
	public static String latestSelectQuery = "";
	
	public static void main(String[] args) {		
		dbAccess = new DatabaseAccessObject();
		appFrame = new ApplicationFrame();
	}
	
	public static boolean connect(){
		appFrame.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
		
		Connection connection;
		DatabaseMetaData dbMetaData;
		ResultSet rs;

		List<String> tableNames = new ArrayList<String>();
				
		// read DB connection info
		Properties props = new Properties();
		try {
			props.load(new FileInputStream("resources/dbprops.properties"));
		} 
		catch (Exception e) {
			appFrame.showErrorMessage("Error reading DB Properties: " + e.getMessage(), "Error");
			appFrame.setCursor(Cursor.getDefaultCursor());
			return false;
		} 
				
		String driver = props.getProperty("Driver");
		String url = props.getProperty("URL");
		String username = props.getProperty("User");
		String password = props.getProperty("Password");
		
		// establish connection and obtain MetaData
		try{
			dbAccess.loadDatabaseDriver(driver);
			dbAccess.connect(url, username, password);
			connection = dbAccess.getConnection();
			dbMetaData = dbAccess.getDatabaseMetadata(connection);
			
			rs = dbMetaData.getTables(null, null, null, new String[] {"TABLE"});
			while(rs.next()){
				tableNames.add(rs.getString(3));
			}			
		}
		catch (Exception e){
			appFrame.showErrorMessage("Failed to initialize DB connection or obtain metadata: " + e.getMessage(), "Error");
			disconnect();
			return false;
		}
		
		// initialise appFrame UI with table names from DB MetaData		
		appFrame.fillTableNameComboBox(tableNames);		
		appFrame.setCursor(Cursor.getDefaultCursor());
		return true;
	}
	
	public static boolean disconnect(){
		try {
			appFrame.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
			dbAccess.closeConnection();
			return true;
		} 
		catch (SQLException ex) {
			appFrame.showErrorMessage("Failed to close DB connection: " + ex.getMessage(), "Error");
			return false;
		}
		finally{
			appFrame.setCursor(Cursor.getDefaultCursor());
		}
	}
	
	/**
	 * Hashtable structure:
	 * columnNames -> column names (Vector<String>)
	 * columnCount -> column count (int)
	 * rowCount    -> row count (int)
	 * columnIsReadOnly -> isReadOnly on each column (Vector<Boolean>)
	 * tableData   -> table data (Vector<Vector<String>>)  
	 */
	public static Hashtable<String, Object> getTableInfo(String selectQuery){	
		Hashtable<String, Object> tableInfo = new Hashtable<String, Object>();
		
		try {	
			appFrame.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
			
			dbAccess.initializeStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			ResultSet resultSet = dbAccess.generateResultSet(selectQuery);
			ResultSetMetaData rsmd = dbAccess.getResultSetMetadata(resultSet);			
								
			// get column and row count
			int columnCount = rsmd.getColumnCount();	
			resultSet.last();
			int rowCount = resultSet.getRow();
			resultSet.beforeFirst();
			
			Vector<String> columnNames = new Vector<String>(columnCount);
			Vector<Boolean> columnIsReadOnly = new Vector<Boolean>(columnCount);
			for(int i = 1; i <= columnCount; i++){
				// get column names
				String columnName = rsmd.getColumnName(i);
				columnNames.add(columnName);
				// get read only information
				boolean isReadOnly = rsmd.isReadOnly(i);
				columnIsReadOnly.add(isReadOnly);
			}
			
			// get table data
			Vector<Vector<String>> tableData = new Vector<Vector<String>>(rowCount);
			while(resultSet.next()){
				Vector<String> rowData = new Vector<String>(columnCount);
				for(int i = 1; i <= columnCount; i++){
					rowData.add(resultSet.getString(i));
				}
				tableData.add(rowData);
			}			
			
			tableInfo.put("columnNames", columnNames);
			tableInfo.put("columnCount", columnCount);
			tableInfo.put("rowCount", rowCount);
			tableInfo.put("tableData", tableData);
			tableInfo.put("columnIsReadOnly", columnIsReadOnly);
			
			latestSelectQuery = selectQuery;
		} 
		catch (SQLException e) {
			appFrame.showErrorMessage("Failed to retrieve table info: " + e.getMessage(), "Error");
		}
		finally{
			appFrame.setCursor(Cursor.getDefaultCursor());
		}
		return tableInfo;
	}
	
	public static boolean updateTableRow(int row, Vector<Object> newValues){
		if(newValues == null || row < 1){
			appFrame.showErrorMessage("Invalid row number or cell values", "Error");
			return false;
		}
		ResultSet rs = dbAccess.getResultSet();
		try{
			appFrame.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
			// if rs null or closed then regenerate
			if(rs == null){
				dbAccess.generateResultSet(latestSelectQuery);
			}
//			else if(rs.isClosed()){
//				dbAccess.generateResultSet(latestSelectQuery);
//			}
			
			rs.absolute(row);
			for(int i = 1; i <= newValues.size(); i++){
				Object nextObj = newValues.get(i - 1);
				// nextObj is null when the associated column is read-only
				if(nextObj != null){
					rs.updateObject(i, nextObj);
				}
			}
	        rs.updateRow();
	        return true;
		}
		catch (SQLException e) {
			appFrame.showErrorMessage("Failed to update table row: " + e.getMessage(), "Error");
			return false;
		}
		finally{
			appFrame.setCursor(Cursor.getDefaultCursor());
		}
	}
	
	public static boolean insertTableRow(Vector<Object> newValues, String tableName){
		if(newValues == null){
			appFrame.showErrorMessage("Invalid row number or cell values", "Error");
			return false;
		}
		String query = "SELECT * FROM " + tableName + ";";
		try{
			appFrame.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
			// build result set for insert operation
			ResultSet rs = dbAccess.generateResultSet(query);		
			rs.moveToInsertRow();
			for(int i = 1; i <= newValues.size(); i++){
				Object nextObj = newValues.get(i - 1);
				// nextObj is null when the associated column is read-only
				if(nextObj != null)
					rs.updateObject(i, nextObj);
			}
			rs.insertRow();
			return true;
		}
		catch (SQLException e) {
			appFrame.showErrorMessage("Failed to insert table row: " + e.getMessage(), "Error");
			return false;
		}
		finally{
			// restore previous result set
			try {
				dbAccess.generateResultSet(latestSelectQuery);
			} catch (SQLException e) {
				appFrame.showErrorMessage("There was an error restoring the previous result set: " + e.getMessage(), "Error");
			}
			appFrame.setCursor(Cursor.getDefaultCursor());
		}
	}
	
	public static boolean deleteTableRow(int row){
		if(row < 0){
			appFrame.showErrorMessage("Invalid row number", "Error");
			return false;
		}
		ResultSet rs = dbAccess.getResultSet();
		try{
			appFrame.setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
			// if rs null or closed then regenerate
			if(rs == null){
				dbAccess.generateResultSet(latestSelectQuery);
			}
//			else if(rs.isClosed()){
//				dbAccess.generateResultSet(latestSelectQuery);
//			}
			rs.absolute(row);
			rs.deleteRow();
			return true;
		}
		catch (SQLException e) {
			appFrame.showErrorMessage("Failed to delete table row: " + e.getMessage(), "Error");
			return false;
		}
		finally{
			appFrame.setCursor(Cursor.getDefaultCursor());
		}
	}
}
